* apply restrictions to MCVL data tables and generate samples
**** OUTLINE ****
* prelims
* restrictions
* generate mcvl samples
* table 01 (personal) to long
* table 03 (contributions) to wide
* closing
********************************************************************************
* prelims																	   
********************************************************************************
{ 
clear all
cap log close
set more off
}
********************************************************************************
* restrictions																	   
********************************************************************************
{
* criteria to be in the sample:
* (1) born before Jan 1st, 1973
* (2) entering unemployment at some point in the working history

* load personal data
use "$data/mcvl2004-16_01pers.dta", clear

* restrict sample according criteria
	* AGE
* (1) born before Jan 1st, 1973 -> at least 42yo in 2015
local agerest 197301
local agerest2004 19730101

g birth = birth2016
forval year = 2015(-1)2005 {
	replace birth = birth`year' if birth=="" & birth`year' != ""
}
destring birth, force replace

keep if (birth < `agerest' | birth2004 < `agerest2004')

* store temp file
keep id
compress
save "$data/temp/mcvl2004_16_res_age.dta", replace

	* EVER UNEMPLOYED
* (2) entering unemployment at some point in the working history
* load work histories
use "$data/mcvl2004-16_02affi.dta", clear

* apply unemployment restriction
gen unemployed = (reltype >= 751 & reltype <= 756)
bysort id: egen ever_unemployed = max(unemployed)
keep if ever_unemployed != 0
keep id
duplicates drop id, force

compress
save "$data/temp/mcvl2004_16_res_un.dta", replace

* merge the two datasets withs ids
use "$data/temp/mcvl2004-2016_all_ids.dta", clear

merge 1:1 id using "$data/temp/mcvl2004_16_res_age.dta", keepusing(id)
keep if _m==3
drop _m

merge 1:1 id using "$data/temp/mcvl2004_16_res_un.dta", keepusing(id)
keep if _m==3
drop _m

* store list of ids meeting the criteria
sort id
compress
save "$data/temp/mcvl2004-2016_rest_age_un.dta", replace

* include 2017	
use "$data/temp/mcvl2004-2017_all_ids.dta", clear

merge 1:1 id using "$data/temp/mcvl2004-2016_rest_age_un.dta"
keep if _m==3
drop _m

drop if mcvl2017==1 & mcvl2016==0

* store
sort id
compress
save "$data/temp/mcvl2004-2017_rest_age_un.dta", replace

}
********************************************************************************
* generate mcvl samples
********************************************************************************
{
* combined tables
foreach table in 01pers 02affi 03cont 04pens 05coha 06fisc {

	* load table
	use "$data/mcvl2004-17_`table'.dta", clear
	* restrict sample
	merge m:1 id using "$data/temp/mcvl2004-2017_rest_age_un.dta", keepusing(id)
	keep if _m==3
	drop _m
	* store
	compress
	save "$data/mcvl2004-17_`table'_rest_age_un.dta", replace

}
}
********************************************************************************
* table 01 (personal) to long													   
********************************************************************************
{

* load data
use "$data/mcvl2004-17_01pers_rest_age_un.dta", clear

* only after 2005 
drop *2004

* reshape
local varsreshape birth nat residence educ country
local ids id gender
reshape long `varsreshape', i(`ids') j(year)

* store
compress
save "$data/mcvl2004-17_01pers_rest_age_un_long.dta", replace

}
********************************************************************************
* table 03 (contributions) to wide
********************************************************************************
{ 

* load contribution
use "$data/mcvl2004-17_03cont_rest_age_un.dta", clear

* only after 2005 
drop if contyear<2005

* aggregate at the year level
rename cotbjan cotb1
rename cotbfeb cotb2
rename cotbmar cotb3
rename cotbapr cotb4
rename cotbmay cotb5
rename cotbjun cotb6
rename cotbjul cotb7
rename cotbaug cotb8
rename cotbsep cotb9
rename cotboct cotb10
rename cotbnov cotb11
rename cotbdec cotb12
* aggregate
forval m=1(1)12 {
	bys id contyear: egen cot`m' = sum(cotb`m')
	drop cotb`m'
}
egen tagidy=tag(id contyear)
keep if tagidy==1
drop tagidy

* reshape to wide
drop ccc cotbtot
reshape wide cot1-cot12, i(id) j(contyear)

* store
sort id
compress
save "$data/mcvl2004-17_03cont_rest_age_un_wide.dta", replace

* erase temp files
cap erase "$data/temp/mcvl2004_16_res_age.dta"
cap erase "$data/temp/mcvl2004_16_res_un.dta"
cap erase "$data/temp/mcvl2004-2016_rest_age_un.dta"
cap erase "$data/temp/mcvl2004-2017_rest_age_un.dta"
cap erase "$data/temp/mcvl2004-2017_all_ids.dta"
cap erase "$data/temp/mcvl2004-2016_all_ids.dta"
cap erase "$data/mcvl2004-16_01pers.dta"
cap erase "$data/mcvl2004-16_02affi.dta"

} 
********************************************************************************
* closing																	   
********************************************************************************
{
cap log close
clear
}
